using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CoreData;
using EntityData;
using Helper;
namespace Services
{

    public partial class Inventory_DetailService
    {
       readonly IInventory_DetailEntity entity;

       public Inventory_DetailService()
       {
           this.entity = new Inventory_DetailEntity();

       }
       private string StrGet_Inventory = @"select vod.ProductID,vod.UnitID,vod.Price,vod.Amount
,sum(vod.Quantity) as Disparity,sum(vod.Quantity) as OldQuantity,p.Code as ProductCode,p.Name as ProductName,pu.Name as UnitName,vo.TypeOfOut,vo.PlaceID 
,isnull(ivd.QuantityPercent,0) as QuantityPercent,0 as NewQuantityPercent
from Stock_VoucherOutDetail vod
left join Stock_VoucherOut vo on vo.ID=vod.Stock_VoucherOutID
left join Product_Unit pu on pu.ID=vod.UnitID
left join Product p on p.ID=vod.ProductID
left join Inventory_Detail ivd on ivd.ProductID=vod.ProductID
where vo.TypeOfOut='XCPH' and {0}
group by P.ID,vod.ProductID,vod.UnitID,vod.Price,vod.Amount,p.Code,p.Name,pu.Name,vo.TypeOfOut,vo.PlaceID,ivd.QuantityPercent";
        //new lay ds san pham o phong hoc
       private string StrGet_InventoryR1 = @"DROP TABLE #temptb
SELECT
vod.ProductID
,SUM(vod.Quantity) AS RowIndex, vod.ID
,vod.UnitID
,vo.TypeOfOut,vo.PlaceID
,ivd.VoucherDetailID
INTO #temptb
FROM Stock_VoucherOutDetail vod
left join Stock_VoucherOut vo on vo.ID=vod.Stock_VoucherOutID
left join Inventory_Detail ivd on ivd.ProductID=vod.ProductID
where vo.TypeOfOut='XCPH' and vod.Quantity > 0
GROUP BY vod.ProductID,vod.ID,vod.ProductID,vod.UnitID,vo.TypeOfOut,vo.PlaceID ,ivd.VoucherDetailID

;WITH recurse (ID, ProductID, RowIndex, cnt,VoucherDetailID) AS
(
   SELECT t.ID, t.ProductID, t.RowIndex, t.RowIndex, t.VoucherDetailID
      FROM #temptb t
   UNION ALL
   SELECT r.ID, r.ProductID, r.RowIndex, r.cnt - 1,r.VoucherDetailID
      FROM recurse r
      WHERE cnt - 1 != 0
)
SELECT  r2.RowIndex,r2.ID, r2.ProductID,r2.VoucherDetailID,'100%' as QuantityPercent, '100%' as NewQuantityPercent
,p.Name as ProductName,pu.Name as UnitName,p.Code as ProductCode
   FROM recurse r2
   left join Inventory_Detail ivd on ivd.ProductID=r2.ProductID	
   left join Product p on r2.ProductID=p.ID
   left join Product_Unit pu on pu.ID=p.Input_UnitID
	where r2.VoucherDetailID is null
	group by r2.RowIndex,r2.ID, r2.ProductID,r2.VoucherDetailID
,p.Name,pu.Name,p.Code
   ORDER BY id";
       //lay sp ton o phong hoc
       private string StrGet_ProductInRoom = @"select O.ProductID,isnull(O.OldQuantity,0)-isnull(N.NewQuantity,0) as InStockQuantity,p.Name as ProductName,p.Code as ProductCode,pu.Name as UnitName 
from
(
select vod.ProductID,sum(vod.Quantity) as OldQuantity,vo.TypeOfOut,vo.PlaceID, vod.UnitID
from Stock_VoucherOutDetail vod
left join Stock_VoucherOut vo on vo.ID=vod.Stock_VoucherOutID
group by vod.ProductID,vo.TypeOfOut,vo.PlaceID,vod.UnitID
)O
left join 
(select SUM(id.NewQuantity) as NewQuantity,i.RoomID, id.ProductID,id.UnitID from Inventory_Detail id
left join Inventory i on i.ID=id.InventoryID
where i.RoomID={1}
group by i.RoomID,id.ProductID,id.UnitID)N on O.ProductID=N.ProductID
left join Product p on p.ID=O.ProductID
left join Product_Unit pu on pu.ID=O.UnitID
where O.TypeOfOut='XCPH' and O.PlaceID= {0}";

        //chi tiet kiem ke
       private string StrGet_InventoryDetail = @"WITH CTE AS (
SELECT ROW_NUMBER() OVER(ORDER BY VoucherDate ASC) AS STT,C.ProductID,C.VoucherDate,C.VoucherNo,C.InputQuantity,C.OutputQuantity,C.PlaceID,C.Note
FROM
(select vod.ProductID,vo.VoucherOutNo as VoucherNo,vo.VoucherDate,sum(vod.Quantity) as InputQuantity,NULL as OutputQuantity,vo.PlaceID,vo.Note
from Stock_VoucherOutDetail vod
left join Stock_VoucherOut vo on vo.ID=vod.Stock_VoucherOutID
where vo.TypeOfOut='XCPH'
group by vod.ProductID,vo.PlaceID,vo.VoucherDate,vo.VoucherOutNo,vo.Note
union
select id.ProductID,i.InventoryNo as VoucherNo,i.InventoryDate as VoucherDate,NULL as InputQuantity,SUM(id.NewQuantity) as OutputQuantity,i.RoomID as PlaceID,i.Note
from Inventory_Detail id
left join Inventory i on i.ID=id.InventoryID
group by i.RoomID,id.ProductID,i.InventoryNo,i.InventoryDate,i.Note)C
{0}
)
SELECT c.STT,c.ProductID,c.VoucherNo,c.VoucherDate
,c.InputQuantity
,c.OutputQuantity
,c.Note
,(select SUM(CTE.InputQuantity) from CTE where CTE.STT<=c.STT)- isnull(c.OutputQuantity,0) as InStockQuantity
FROM CTE c
left join Product p on p.ID=c.ProductID

";
       private string GetProductBeginStock = @"select BD.ProductID,B.BeginStockNo as VoucherNo,B.BeginStockDate as VoucherDate 
,0 as InputQuantity, 0 as OutputQuantity, BD.NewQuantity as InStockQuantity, P.Name as Note
from BeginStock_Detail BD
left join Product P on P.ID=BD.ProductID
left join BeginStock B on B.ID=BD.BeginStockID {0}";
       #region Main Method

       /// <summary>
       /// Save 
       /// </summary>
       /// <param name="_model"></param>
       /// <returns></returns>
       public long Save(object _model)
       {
           try
           {
               var id = long.Parse(_model.GetType().GetProperty("ID").GetValue(_model, null).ToString());
               if (id == 0)
               {
                   return entity.Save((CoreData.Inventory_Detail)_model, Table.Inventory_Detail.ToString());
               }
               else
               {
                   return entity.Update((CoreData.Inventory_Detail)_model, Table.Inventory_Detail.ToString());
               }
           }
           catch { return -1; }
       }

       /// <summary>
       /// Get by ID
       /// </summary>
       /// <param name="_id"></param>
       /// <returns></returns>
       public CoreData.Inventory_Detail GetByID(long _id)
       {
           try
           {
               return entity.GetById(_id, Table.Inventory_Detail.ToString());
           }
           catch { return null; }
       }

       /// <summary>
       /// Get List
       /// </summary>
       /// <returns></returns>
       public IList<CoreData.Inventory_Detail> GetList()
       {
           try
           {
               return entity.GetAll(Table.Inventory_Detail.ToString()).ToList();
           }
           catch { return null; }

       }

       /// <summary>
       /// Get List with Linq
       /// </summary>
       /// <returns></returns>
       public IList<CoreData.Inventory_Detail> GetListByLINQ(Func<CoreData.Inventory_Detail, Boolean> _where)
       {
           try
           {
               return entity.GetMany(_where, Table.Inventory_Detail.ToString()).ToList();
           }
           catch { return null; }
       }
       /// <summary>
       /// Get One with Linq
       /// </summary>
       /// <returns></returns>
       public CoreData.Inventory_Detail GetOneByLINQ(Func<CoreData.Inventory_Detail, Boolean> _where)
       {
           try
           {
               return entity.Get(_where, Table.Inventory_Detail.ToString());
           }
           catch { return null; }
       }

       /// <summary>
       /// Get List with search string
       /// </summary>
       /// <returns></returns>
       public IList<CoreData.Inventory_Detail> GetList(string _searchstring)
       {
           //search theo tieu chi nao do         
           try
           {
               return entity.GetBySearchString(_searchstring, Table.Inventory_Detail.ToString()).ToList();
           }
           catch { return null; }
       }


       /// <summary>
       /// Delete
       /// </summary>
       /// <param name="_model"></param>
       /// <returns></returns>
       public bool Delete(object _model)
       {
           try
           {
               entity.Delete((CoreData.Inventory_Detail)_model, Table.Inventory_Detail.ToString());
               return true;
           }
           catch { return false; }
       }


       #endregion



        #region Other Method

       public IList<CoreData.Inventory_Detail> GetList_Inventory(string _searchstring)
       {
           //search theo tieu chi nao do         
           try
           {
               return entity.GetByCusTomSQL(string.Format(StrGet_Inventory,_searchstring)).ToList();
           }
           catch { return null; }
       }
       public IList<CoreData.Inventory_Detail> GetList_ProductInRoom(long _RoomID)
       {
           //search theo tieu chi nao do         
           try
           {
               return entity.GetByCusTomSQL(string.Format(StrGet_ProductInRoom, _RoomID,_RoomID)).ToList();
           }
           catch { return null; }
       }
        //lay ds sp va sl ton dau ki de xem
       public IList<CoreData.Inventory_Detail> GetList_ProductBeginStock(string _where)
       {
           //search theo tieu chi nao do         
           try
           {
               return entity.GetByCusTomSQL(string.Format(GetProductBeginStock,  _where)).ToList();
           }
           catch { return null; }
       }
       public IList<CoreData.Inventory_Detail> GetList_InventoryDetail(long _RoomID,long ProductID)
       {
           //search theo tieu chi nao do         
           try
           {
               string _search = "where C.PlaceID=" + _RoomID + " AND C.ProductID=" + ProductID;
               return entity.GetByCusTomSQL(string.Format(StrGet_InventoryDetail, _search)).ToList();
           }
           catch { return null; }
       }
        #endregion 
    
    }
         
}
